package com.sam;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Driver;

public class MemberDAO {
        public static Connection conn = null;

        public static Connection getConnection() {
                if (conn == null) {
                        try {
                                DriverManager.registerDriver(new Driver());
                                conn = DriverManager.getConnection(
                                                "jdbc:mysql://j.snpy.org/j102u17", "j102u17", "j102u17.989");
                        } catch (SQLException e) {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                        }
                }
                
                System.out.println("Member DAO conn " + conn);
                
                return conn;

        }

        public List<Member> getAll() {
                List<Member> members = new ArrayList<Member>();
                try {
                        Statement stmt = getConnection().createStatement();
                        ResultSet rs = stmt.executeQuery("select * from users");
                        while (rs.next()) {
                                String userid = rs.getString("userid");	
                                String username = rs.getString("username");
                                String nickname = rs.getString("nickname");
                                String password = rs.getString("password");
                                String email = rs.getString("email");
                                String status = rs.getString("status");
                                Member member = new Member(userid, username, nickname, password, email, status);
                                members.add(member);
                        }
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return members;
        }

        public int delete(String userid) {
                String sql = "delete from users where userid=?";
                int rowCount = 0;
                try {
                        PreparedStatement pstmt = getConnection().prepareStatement(sql);
                        pstmt.setString(1, userid);
                        rowCount = pstmt.executeUpdate();
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return rowCount;
        }
        
        public int add(Member m){
                String sql = "insert into users(id,username,nickname,password,email,status) values (?,?,?,?,?,?)";
                int rowCount = 0;
                try {
                        PreparedStatement pstmt = getConnection().prepareStatement(sql);
                        pstmt.setString(1, m.getuserid());
                        pstmt.setString(2, m.getusername());
                        pstmt.setString(3, m.getnickname());
                        pstmt.setString(4, m.getpassword());
                        pstmt.setString(5, m.getemail());
                        pstmt.setString(6, " ");
                        rowCount = pstmt.executeUpdate();
                        System.out.println(rowCount);
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return rowCount;
        }
        
        public int update(Member m){
                String sql = "update users set username?,nickname=?,password=?,email=?  where userid=?";
                int rowCount = 0;
                try {
                        PreparedStatement pstmt = getConnection().prepareStatement(sql);
                        pstmt.setString(1, m.getusername());
                        pstmt.setString(2, m.getnickname());
                        pstmt.setString(3, m.getpassword());
                        pstmt.setString(4, m.getemail());
                        pstmt.setString(5, m.getuserid());
                        rowCount = pstmt.executeUpdate();
                        System.out.println("delrowCount = " + rowCount );
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return rowCount;
        }

        public Member getMemberById(String userid){
                Member member = null;
                String sql = "select * from users where userid=?";
                try {
                        PreparedStatement pstmt = getConnection().prepareStatement(sql);
                        pstmt.setString(1, userid);
                        ResultSet rs = pstmt.executeQuery();
                        if (rs.next()){
                                member = new Member(rs.getString("userid")
                                                , rs.getString("userkname")
                                                , rs.getString("nickname")
                                                , rs.getString("password")
                                                , rs.getString("email")
                                                , rs.getString("status"));
                        }
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return member;
        }

        
    	
}